package co.fitstart.scheduler.service.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.demo2do.core.utils.DateFormatUtils;
import com.demo2do.core.utils.DateUtils;

import co.fitstart.scheduler.Constant;
import co.fitstart.scheduler.entity.RevenueReport;
import co.fitstart.scheduler.persistence.GenericJdbcSupport;
import co.fitstart.scheduler.service.RevenueReportService;

@Service("revenueReportService")
public class RevenueReportServiceImpl implements RevenueReportService{
    
    @Autowired
    private GenericJdbcSupport genericJdbcSupport;

    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.RevenueReportService#createRevenueReportService()
     */
    public void createPurchaseReport() {

        List<RevenueReport> reports = this.getPurchaseReports();
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES (false, :occurTime, :offer, 0, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.RevenueReportService#createFixedRefundReport()
     */
    @Override
    public void createFixedPurchaseReport() {
        List<RevenueReport> reports = this.getFixedPurchaseReports();
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES (false, :occurTime, :offer, 0, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    public void createPurchaseReport(Date day) {
        List<RevenueReport> reports = this.getPurchaseReports(day);
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES (false, :occurTime, :offer, 0, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    @Override
    public void createFixedPurchaseReport(Date start, Date end) {
        List<RevenueReport> reports = this.getFixedPurchaseReports(start, end);
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES (false, :occurTime, :offer, 0, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    /**
     * 
     * @return
     */
    private List<RevenueReport> getPurchaseReports() {
        
        String sentence = "SELECT orders.price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "orders.pay_time as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_TYPE + ") AND "
                              + "orders.payment_status IN (" + Constant.REVENUE_REPORT_PAID + "," + Constant.PAYMENT_STATUS_REFUNDING + "," + Constant.PAYMENT_STATUS_REFUNDED + "," + Constant.PAYMENT_STATUS_SYSTEMREFUNDED + ") AND "
                              + "pay_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), RevenueReport.class);
    }
    
    private List<RevenueReport> getFixedPurchaseReports() {
        
        String sentence = "SELECT orders.price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "orders.pay_time as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_FIXED_TYPE + ") AND "
                              + "orders.payment_status IN (" + Constant.REVENUE_REPORT_PAID + "," + Constant.PAYMENT_STATUS_REFUNDING + "," + Constant.PAYMENT_STATUS_REFUNDED + "," + Constant.PAYMENT_STATUS_SYSTEMREFUNDED + ") AND "
                              + "pay_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), RevenueReport.class);
    }
    
    private List<RevenueReport> getPurchaseReports(Date day) {
        
        String sentence = "SELECT orders.price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "orders.pay_time as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_TYPE + ") AND "
                              + "orders.payment_status IN (" + Constant.REVENUE_REPORT_PAID + "," + Constant.PAYMENT_STATUS_REFUNDING + "," + Constant.PAYMENT_STATUS_REFUNDED + "," + Constant.PAYMENT_STATUS_SYSTEMREFUNDED + ") AND "
                              + "pay_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), RevenueReport.class);
    }
    
    private List<RevenueReport> getFixedPurchaseReports(Date start, Date end) {
        
        String sentence = "SELECT orders.price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "orders.pay_time as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_FIXED_TYPE + ") AND "
                              + "orders.payment_status IN (" + Constant.REVENUE_REPORT_PAID + "," + Constant.PAYMENT_STATUS_REFUNDING + "," + Constant.PAYMENT_STATUS_REFUNDED + "," + Constant.PAYMENT_STATUS_SYSTEMREFUNDED + ") AND "
                              + "pay_time BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateBetweenParams(start, end), RevenueReport.class);
    }
    
    /*
     * (non-Javadoc)
     * @see co.fitstart.scheduler.service.RevenueReportService#createRefundReport()
     */
    public void createRefundReport() {
        
        List<RevenueReport> reports = this.getRefundReports();
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES ( false, :occurTime, :offer, 1, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    @Override
    public void createFixedRefundReport() {
        List<RevenueReport> reports = this.getFixedRefundReports();
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES ( false, :occurTime, :offer, 1, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    public void createRefundReport(Date day) {
        List<RevenueReport> reports = this.getRefundReports(day);
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES ( false, :occurTime, :offer, 1, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    public void createFixedRefundReport(Date start, Date end) {
        List<RevenueReport> reports = this.getFixedRefundReports(start, end);
        
        for(RevenueReport report : reports) {
            
            String insertSentence = "INSERT INTO `revenue_report` (`is_disabled`,`occur_time`,`offer`,`revenue_type`,`student_name`,`student_mobile`,`salesman`,`course_id`,`site_id`,`course_round_id`) "
                                                        + "VALUES ( false, :occurTime, :offer, 1, :studentName, :studentMobile, :salesman, :courseId, :siteId, :courseRoundId)";
            Map<String, Object> insertParameters = report.describe();
            
            genericJdbcSupport.executeSQL(insertSentence, insertParameters);
        }
    }
    
    
    /**
     * 
     * @return
     */
    private List<RevenueReport> getRefundReports() {
        String sentence = "SELECT orders.refund_price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "attr.value as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM order_attr attr, student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.id = attr.order_id AND attr.name='" + Constant.REVENUE_REPORT_REFOUND_ATTR_NAME + "' AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_TYPE +") AND "
                              + "orders.payment_status in (" + Constant.REVENUE_REPORT_REFOUND_PAYMENT_STATUS  + ") AND "
                              + "attr.value BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), RevenueReport.class);
    }
    
    private List<RevenueReport> getFixedRefundReports() {
        String sentence = "SELECT orders.refund_price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "attr.value as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM order_attr attr, student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.id = attr.order_id AND attr.name='" + Constant.REVENUE_REPORT_REFOUND_ATTR_NAME + "' AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_FIXED_TYPE +") AND "
                              + "orders.payment_status in (" + Constant.REVENUE_REPORT_REFOUND_PAYMENT_STATUS  + ") AND "
                              + "attr.value BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(), RevenueReport.class);
    }

    private List<RevenueReport> getRefundReports(Date day) {
        String sentence = "SELECT orders.refund_price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "attr.value as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM order_attr attr, student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.id = attr.order_id AND attr.name='" + Constant.REVENUE_REPORT_REFOUND_ATTR_NAME + "' AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_TYPE +") AND "
                              + "orders.payment_status in (" + Constant.REVENUE_REPORT_REFOUND_PAYMENT_STATUS  + ") AND "
                              + "attr.value BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateYesterdayParams(day), RevenueReport.class);
    }
    
    private List<RevenueReport> getFixedRefundReports(Date start, Date end) {
        String sentence = "SELECT orders.refund_price as offer, "
                               + "student.name as studentName, "
                               + "student.mobile as studentMobile, "
                               + "salesman.name as salesman, "
                               + "attr.value as occurTime, "
                               + "orders.course_id as courseId, "
                               + "orders.site_id as siteId, "
                               + "round.id as courseRoundId "
                        + "FROM order_attr attr, student student, course_round round, orders orders LEFT JOIN principal salesman ON orders.salesman_id = salesman.id "
                        + "WHERE orders.student_id = student.id AND "
                              + "orders.id = round.order_id AND "
                              + "orders.id = attr.order_id AND attr.name='" + Constant.REVENUE_REPORT_REFOUND_ATTR_NAME + "' AND "
                              + "orders.is_disabled = false AND "
                              + "orders.order_type in (" + Constant.REVENUE_REPORT_ORDER_FIXED_TYPE +") AND "
                              + "orders.payment_status in (" + Constant.REVENUE_REPORT_REFOUND_PAYMENT_STATUS  + ") AND "
                              + "attr.value BETWEEN :beginDate AND :endDate";
        
        return genericJdbcSupport.queryForList(sentence, this.generateBetweenParams(start, end), RevenueReport.class);
    }
    
    /**
     * 
     * @return
     */
    private Map<String, Object> generateYesterdayParams() {
        
        Date yesterday = DateUtils.addDays(new Date(), -1);
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }

    private Map<String, Object> generateYesterdayParams(Date day) {
        
        Date yesterday = DateUtils.addDays(day, -1);
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }
    
    private Map<String, Object> generateBetweenParams(Date start, Date end) {
        
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("beginDate", DateUtils.parseDate(DateFormatUtils.format(start, "yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss"));
        parameters.put("endDate", DateUtils.parseDate(DateFormatUtils.format(end, "yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss"));
        
        return parameters;
    }
}
